package com.mzm.dev.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.StatementCallback;
import org.valire.db.DbMetaDataUtil;

public class DbDevUtils {

	@Resource
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<UpdateStatus> delete(DbCategory... categories) {
		List<UpdateStatus> statuses = new ArrayList<UpdateStatus>();
		for (DbCategory dbCategory : categories) {
			switch (dbCategory) {
			case ALL:
				statuses.addAll(deleteAll());
				break;
			case EVENTS:
				statuses.addAll(deleteEvents());
				break;
			case RESULTS:
				statuses.addAll(deleteResults());
				break;
			case SESSIONS:
				statuses.addAll(deleteSessions());
				break;
			}

		}
		return statuses;
	}

	private List<UpdateStatus> deleteAll() {
		List<UpdateStatus> statuses = new ArrayList<UpdateStatus>();
		statuses.addAll(deleteBasics());
		statuses.addAll(deleteEvents());
		statuses.addAll(deleteSessions());
		statuses.addAll(deleteResults());
		return statuses;
	}

	public List<UpdateStatus> deleteAll2() {
		DbMetaDataUtil dbMetaDataUtil = new DbMetaDataUtil(jdbcTemplate.getDataSource());
		List<String> listTables = dbMetaDataUtil.listTables();
		StringBuilder sb = new StringBuilder();
		for (String tableName : listTables) {
			if (tableName.startsWith("mysql.")) {
				continue;
			}
			sb.append("truncate table ");
			sb.append(tableName);
			sb.append(";\n");
		}
		List<UpdateStatus> statuses = new ArrayList<UpdateStatus>();
		statuses.addAll(executeSqlBatch(sb.toString()));
		statuses.addAll(creationInserts());
		return statuses;
	}

	private List<UpdateStatus> deleteBasics() {
		InputStream is = getResourceAsStream("delete_basic.sql");
		String sql = stream2String(is);
		return executeSqlBatch(sql);
	}

	private List<UpdateStatus> creationInserts() {
		InputStream is = getResourceAsStream("creation_inserts.sql");
		String sql = stream2String(is);
		return executeSqlBatch(sql);
	}

	private List<UpdateStatus> deleteEvents() {
		InputStream is = getResourceAsStream("delete_events.sql");
		String sql = stream2String(is);
		return executeSqlBatch(sql);
	}

	private List<UpdateStatus> deleteSessions() {
		InputStream is = getResourceAsStream("delete_sessions.sql");
		String sql = stream2String(is);
		return executeSqlBatch(sql);
	}

	private List<UpdateStatus> deleteResults() {
		InputStream is = getResourceAsStream("delete_results.sql");
		String sql = stream2String(is);
		return executeSqlBatch(sql);
	}

	@SuppressWarnings("unchecked")
	private List<UpdateStatus> executeSqlBatch(String batch) {
		String[] sqls = StringUtils.split(batch, ";");
		List<UpdateStatus> statuses = new ArrayList<UpdateStatus>();
		for (final String sql : sqls) {
			final String sq = sql.trim();
			if (sq.isEmpty()) {
				continue;
			}
			// System.err.println("|"+sq+"|");

			int rowsAffected = (Integer) jdbcTemplate.execute(new StatementCallback() {

				@Override
				public Object doInStatement(Statement stmt) throws SQLException, DataAccessException {
					return stmt.executeUpdate(sq);
				}
			});
			statuses.add(new UpdateStatus(sq, rowsAffected));
		}
		return statuses;
	}

	private InputStream getResourceAsStream(String s) {
		InputStream is = getClass().getResourceAsStream(s);
		return is;
	}

	private String stream2String(InputStream is) {
		try {
			return IOUtils.toString(is);
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
}
